﻿Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Public Class oDocHeader
    Public Function UpdateJsonDocHeader(ByVal docEntry As Integer, ByVal companyCode As String, ByVal customerCode As String, ByVal customerName As Boolean, ByVal docDate As Date, _
                                       ByVal docDueDate As Date, ByVal remarks As String, ByVal subTotal As Double, ByVal createUserID As String, _
                                       ByVal docStatus As String, ByVal databaseName As String, ByVal isInsert As Boolean) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(databaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {companyCode, customerCode, customerName, docDate, docDueDate, remarks, subTotal, createUserID, docStatus}
                Dim query As New StringBuilder
                If isInsert = True Then
                    query.Append("INSERT INTO [oDocHeader] ")
                    query.Append(" ,[CompanyCode]")
                    query.Append(" ,[CustomerCode]")
                    query.Append(" ,[CustomerName]")
                    query.Append(" ,[DocDate]")
                    query.Append(" ,[DocDueDate]")
                    query.Append(" ,[Remark]")
                    query.Append(" ,[SubTotal]")
                    query.Append(" ,[CreatedUserID]")
                    query.Append(" ,[DocStatus])")
                    query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9)")
                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
                Else
                    query.Append("UPDATE  [oDocHeader] SET ")
                    query.Append(" [CustomerCode] = @Param1")
                    query.Append(",[CustomerName] = @Param2")
                    query.Append(",[DocDate] = @Param3")
                    query.Append(",[DocDueDate] = @Param4")
                    query.Append(",[Remark] = @Param5")
                    query.Append(",[SubTotal] = @Param6")
                    query.Append(" WHERE [DocEntry] = @Param7")
                    connect.Object_Execute_SAP(query.ToString(), New Object() {customerCode, customerName, docDate, docDueDate, remarks, subTotal, docEntry})
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception

            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function GetCountDocument(ByVal dataBaseName As String, ByVal companyCode As String) As Long
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oDocHeader")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                ds = connect.ObjectGetAll_Query_SAP("SELECT COUNT(*) AS TotalRow FROM oDocHeader WHERE CompanyCode = @Param1", New Object() {companyCode})
                If ds IsNot Nothing AndAlso ds.Tables.Count > 0 AndAlso ds.Tables(0).Rows.Count > 0 Then
                    Return Long.Parse(ds.Tables(0).Rows(0)("TotalRow").ToString())
                End If
            End If
            Return 0
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetAllDocHeader(ByVal dataBaseName As String, ByVal companyCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oDocHeader")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT * FROM oDocHeader T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.DocStatus = T2.ReferenceValue AND  T2.ReferenceCode ='DocStatus')")
                query.Append(" WHERE T0.CompanyCode  = @Param1 ")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetLastRecordDocHeader(ByVal dataBaseName As String, ByVal companyCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oDocHeader")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT TOP 1 * FROM oDocHeader T0 JOIN oCompany T1 ON T0.CompanyCode  = T1.CompanyCode ")
                query.Append(" LEFT JOIN oReferenceValue T2 ON (T0.DocStatus = T2.ReferenceValue AND  T2.ReferenceCode ='DocStatus')")
                query.Append(" WHERE T0.CompanyCode  = @Param1 ")
                query.Append(" ORDER BY DocEntry DESC ")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetDocHeaderByDocEntry(ByVal dataBaseName As String, ByVal companyCode As String, ByVal docEntry As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oDocHeader")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                ds = connect.ObjectGetAll_Query_SAP("SELECT * FROM oDocHeader T0 LEFT JOIN oReferenceValue T1 ON (T0.DocStatus = T1.ReferenceValue AND  T1.ReferenceCode ='DocStatus') WHERE DocEntry = @Param1 AND T0.CompanyCode = @Param2", New Object() {docEntry, companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetDocHeaderByCustomerForSearch(ByVal dataBaseName As String, ByVal companyCode As String, ByVal keySearch As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oDocHeader")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                ds = connect.ObjectGetAll_Query_SAP("SELECT * FROM oDocHeader T0 LEFT JOIN oReferenceValue T1 ON (T0.DocStatus = T1.ReferenceValue AND  T1.ReferenceCode ='DocStatus') WHERE T0.CompanyCode = @Param1 AND (T0.CustomerCode LIKE '%' + @Param2 + '%' OR T0.CustomerName LIKE '%' + @Param2 + '%' )", New Object() {companyCode, keySearch})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function CreateMarketingDocument(ByVal dataBaseName As String, ByVal docEntry As String, ByVal isUpdate As Boolean, ByVal headerJson As String, ByVal lineJson As String) As String
        Dim connect As New Connection()
        Try
            Dim jss As New JavaScriptSerializer()
            Dim header As oHeader = jss.Deserialize(Of oHeader)(headerJson)
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 AndAlso header IsNot Nothing Then

                'Get customer Infor
                Dim customer As New oCustomer
                Dim customerCode As String = String.Empty
                Dim dsCustomer As DataSet = customer.GetCustomerByID(dataBaseName, header.CustomerID)
                If dsCustomer IsNot Nothing AndAlso dsCustomer.Tables.Count > 0 AndAlso dsCustomer.Tables(0).Rows.Count > 0 Then
                    customerCode = dsCustomer.Tables(0).Rows(0)("CustomerCode")
                End If

                Dim query As New StringBuilder
                If isUpdate = False Then

                    query.Append("INSERT INTO [oDocHeader] ")
                    query.Append("  ([CompanyCode]")
                    query.Append("  ,[CustomerID]")
                    query.Append("  ,[CustomerName]")
                    query.Append("  ,[CustomerCode]")
                    query.Append("  ,[DocDate]")
                    query.Append("  ,[DocTime]")
                    query.Append("  ,[DocDueDate]")
                    query.Append("  ,[Remark]")
                    query.Append("  ,[SubTotal]")
                    query.Append("  ,[CreatedUserID]")
                    query.Append("  ,[DocStatus])")
                    query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11)")

                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {header.CompanyCode, header.CustomerID, header.CustomerName, customerCode, _
                                           header.DocDate, header.DocTime, header.DocDueDate, header.Remark, header.SubTotal, header.CreatedUserID, header.DocStatus})
                    If (count > 0) Then
                        'Get Max Id
                        Dim ds As DataSet = connect.ObjectGetAll_Query_SAP("SELECT IDENT_CURRENT('oDocHeader')")
                        'Insert DocLine
                        If ds IsNot Nothing And ds.Tables.Count > 0 Then
                            Dim objLine As New oDocLine
                            Dim newDocEntry As Integer = Integer.Parse(ds.Tables(0).Rows(0)(0).ToString())
                            errMsg = objLine.CreateMarketingDocument(dataBaseName, newDocEntry, isUpdate, lineJson)
                            If errMsg.Length = 0 Then
                                'Update DocPromotion
                                Dim dicParams As New Dictionary(Of String, String)
                                dicParams.Add("@DocEntry", newDocEntry)
                                connect.Object_Execute_Stored("sp_Promotion", dicParams)
                                Return newDocEntry
                            Else
                                DeleteDocument(dataBaseName, newDocEntry)
                            End If
                        End If
                    End If
                Else
                    query.Append("IF EXISTS (SELECT 1 FROM oDocHeader WHERE [DocEntry] = @Param10 AND (DocStatus IS NULL OR DocStatus <> 2) )")
                    query.Append(" UPDATE  [oDocHeader] SET ")
                    query.Append("  [CustomerID] = @Param1")
                    query.Append("  ,[CustomerCode] = @Param2")
                    query.Append("  ,[CustomerName] =@Param3")
                    query.Append("  ,[DocDate] = @Param4")
                    query.Append("  ,[DocTime] = @Param5")
                    query.Append("  ,[DocDueDate] = @Param6")
                    query.Append("  ,[Remark] = @Param7")
                    query.Append("  ,[SubTotal] = @Param8")
                    query.Append("  ,[DocStatus] = @Param9")
                    query.Append(" WHERE [DocEntry] = @Param10")
                    Dim count = connect.Object_Execute_SAP(query.ToString(), New Object() {header.CustomerID, customerCode, header.CustomerName, _
                                                                                header.DocDate, header.DocTime, header.DocDueDate, header.Remark, _
                                                                                header.SubTotal, header.DocStatus, header.DocEntry})
                    If count > 0 Then
                        Dim objLine As New oDocLine
                        errMsg = objLine.CreateMarketingDocument(dataBaseName, docEntry, isUpdate, lineJson)
                        If errMsg.Length = 0 Then
                            If header.DocStatus = 1 Then
                                'Update DocPromotion
                                Dim dicParams As New Dictionary(Of String, String)
                                dicParams.Add("@DocEntry", docEntry)
                                Dim countStored As Integer = connect.Object_Execute_Stored("sp_Promotion", dicParams)
                                If countStored = -2 Then
                                    Return "Can not exe sp_Promotion"
                                End If
                            ElseIf header.DocStatus = 2 Then
                                'Update DocPromotion
                                Dim dicParams As New Dictionary(Of String, String)
                                dicParams.Add("@DocEntry", docEntry)
                                Dim countStored As Integer = connect.Object_Execute_Stored("sp_Promotion_Point", dicParams)
                                If countStored = -2 Then
                                    Return "Can not exe sp_Promotion_Point"
                                End If
                            End If
                            Return docEntry
                        End If
                    End If
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function DeleteDocument(ByVal dataBaseName As String, ByVal docEntry As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {docEntry}
                Dim query As New StringBuilder
                query.Append(" DELETE FROM [oDocHeader]")
                query.Append("  WHERE oDocHeader.DocEntry =  @Param1")
                query.Append(" DELETE FROM [oDocLine]")
                query.Append("  WHERE oDocLine.DocEntry =  @Param1")
                Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function ClosedMarketingDocument(ByVal dataBaseName As String, ByVal docEntry As String, ByVal docStatus As String) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("UPDATE  [oDocHeader] SET ")
                query.Append("  [DocStatus] = @Param1")
                query.Append(" WHERE [DocEntry] = @Param2")
                Dim count = connect.Object_Execute_SAP(query.ToString(), New Object() {docStatus, docEntry})
                If count > 0 Then
                    If docStatus = 2 Then
                        'Update DocPromotion
                        Dim dicParams As New Dictionary(Of String, String)
                        dicParams.Add("@DocEntry", docEntry)
                        Dim countStored As Integer = connect.Object_Execute_Stored("sp_Promotion_Point", dicParams)
                        If countStored = -2 Then
                            Return "Can not exe sp_Promotion_Point"
                        End If
                    End If
                    Return docEntry
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Class oHeader
        Property DocEntry As Integer
        Property CompanyCode As String
        Property CompanyName As String
        Property CustomerCode As String
        Property CustomerID As String
        Property CustomerName As String
        Property DocDate As DateTime
        Property DocTime As DateTime
        Property DocDueDate As DateTime
        Property Remark As String
        Property SubTotal As Double
        Property CreatedUserID As Integer
        Property CreatedDate As DateTime
        Property DocStatus As Integer
        Public Sub New()
        End Sub
    End Class
End Class

